Oracle SQL Revealed by Alex Reprintsev

Author:Alex Reprintsev
Language: eng
Format: epub
Publisher: Apress, Berkeley, CA

At the same time, it can be easily calculated using analytic functions

select id, value, value / first_value(value) over(order by id) ratio from t

Here is a more synthetic example: calculate the ratio between the current row value and the value from the row referenced by ref_id.

exec dbms_random.seed(100);

create table t as

select rownum id,

100 + rownum - 1 value,

trunc(dbms_random.value(1, 10 + 1)) ref_id

from dual

connect by level <= 10;

Model solution is below :

select *

from t

model

dimension by (id)

measures (value, ref_id, 0 ratio)

rules

(

ratio[any] order by id =

round(value[cv(id)] / value[ref_id[cv(id)]], 3)

);

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.